Taeluf.com

src.php

<?php


/**
 * Export .ods files to csv files using `libreoffice` command
 *
 */
function export_to_csv($odsDir, $csvDir){
    // this old command produced not utf8 output so was an issue
    // $cmd = "libreoffice --headless --convert-to csv \"$odsDir\"/* --outdir \"$csvDir\"";
    

    // the --infilter suggestion comes from https://unix.stackexchange.com/questions/259361/specify-encoding-with-libreoffice-convert-to-csv
    $cmd = "libreoffice --headless --convert-to csv --infilter=CSV:44,34,76,1 \"$odsDir\"/* --outdir \"$csvDir\"";

    //there is an alternate solution requiring an additional step for file format conversion available at: https://superuser.com/questions/199799/vim-shows-strange-characters-91-92#

    system($cmd);
}

/**
 * create json files to mirror the csv files, but remove extraneous columns first
 */
function csv_to_clean_json($csvDir, $jsonDir){

    foreach (scandir($csvDir) as $file){
        if ($file=='.'||$file=='..')continue;
        // if ($file!='idph-cases.csv')continue;
        $jsonFileName = pathinfo($file, PATHINFO_FILENAME).'.json';

        echo "\nStart file '$file'\n   ";
        $path = $csvDir.'/'.$file;
        $csv = League\Csv\Reader::createFromPath($path, 'r');
        // row 9 has db column names
        // row 10 has spreadsheet column names (human friendly)
        // row 11 (offset 10) has first row of data
        $csv->setHeaderOffset(8); 
        $header = $csv->getHeader(); //returns the CSV header record

        $cols = [];
        foreach ($header as $index=>$colName){
            if (trim($colName)==''||substr($colName,0,2)=='--')break;
            $cols[] = $colName;
        }

        $json_out = [];
        $f = fopen($jsonPath=$jsonDir.'/'.$jsonFileName, 'w');
        if (!$f){
            throw new \Exception("Could not open ".$jsonPath);
        }
        fwrite($f, "[\n");
        $has_written = false;
        foreach ($csv->getRecords($cols) as $offset => $record){
            if ($offset<10)continue;
            $record = clean_row($cols,$record);
            if ($record == null)continue;
            if ($has_written) fwrite($f,','."\n");
            fwrite($f,json_encode($record));
            $has_written = true;
        }
        fwrite($f, "\n]");
        fclose($f);
    }


}

function json_to_sql($jsonDir, $sqlDir){

    $out = $sqlDir; // prefix for .json, -gen.json, .sql

    foreach (scandir($jsonDir) as $file){
        if ($file == '..' || $file == '.')continue;
        echo "\nGenerate sql for $file";
        $fname = pathinfo($file, PATHINFO_FILENAME);
        $tableName = str_replace(['-',' '],'_', $fname);
        $outDir = $sqlDir.'/'.$fname;
        if (!is_dir($outDir))mkdir($outDir);
        $source = $jsonDir.$file;
        $jsonToSql = new JSONToTable($source, $outDir);
        $jsonToSql->generateSchema(true);
        $jsonToSql->generateSql($tableName,true, 1024*1024*1024);
    }

}

/**
 *
 * @see clean_value()
 * @return an array with cleaned-up data
 */
function clean_row($cols, $row){
    if (trim($row[$cols[0]])==''
        ||trim($row[$cols[1]])=='')return null;
    $out = [];
    foreach ($row as $key=>$value){
        $value = clean_value($key,$value);
        $out[$key] = $value;
    }

    return $out;
}


/**
 * - Rounds percents to 2 decimal places & casts to float
 * - converts 'na', 'n/a', and empty string to null
 *
 * @param $key the key for the column
 * @param $value the value to modify
 *
 * @return a cleaned up version of the value (sometimes null)
 */
function clean_value($key, $value){
    if (substr($value,-1)=='%'){
        $value = (float)substr($value,0,-1);
        $value = round($value,2);
        // 2 decimal places & remove the %
    } else if ($value=='na'||$value=='n/a'){
        $value = null;
    } else if ($value === ''){
        $value = null;
    }

    return $value;
}


/**
 *
 * @param $sqlDir the root dir for taluf/json-to-table's output
 * @param $databasePath the sqlite database path
 */
function execute_sql($sqlDir, $databasePath){
    if (is_file($databasePath))unlink($databasePath);
    $pdo = new \PDO('sqlite:'.$databasePath);
    foreach (scandir($sqlDir) as $dir){
        if ($dir == '.' || $dir == '..')continue;
        $path = $sqlDir.'/'.$dir;
        if (!is_dir($path))continue;
        $pass = 0;
        echo "\nExec ".basename($dir).': ';
        false!==$pdo->exec(file_get_contents($path.'/sql-gen-create.sql')) && $pass++;
        $msg1 = print_r($pdo->errorInfo(),true);
        false!==$pdo->exec(file_get_contents($path.'/sql-gen-insert-0.sql')) && $pass++;
        $msg2 = print_r($pdo->errorInfo(),true);

        if ($pass == 2)echo "success";
        else {
            echo "fail(".$pass.")";
            echo "\n   - ".$msg1;
            echo "\n   - ".$msg2;
        }
    }
}